set nocount on

select acAcct, sum(anDebit-anCredit)
from tHE_AcctTransItem
where ackey like '19%'
and (acAcct like '5%' or acAcct like '6%')
group by acAcct
having sum(anDebit-anCredit) <> 0
-----------------------------------------------------------------------------------------------------------
if not exists (select * from tHE_AcctTrans where acKey = '2040000000001')
begin
	insert into tHE_AcctTrans (acKey, acDoctype, adDate, adDateOfEntry, anDebit, anCredit, acLocked, acNote)
	values('2040000000001', '4000', '2020-01-01', '2020-01-01', 0, 0, 'F', 'Poetno stanje 2020')
end

if not exists (select * from tHE_AcctTrans where acKey = '1949000000001')
begin
	insert into tHE_AcctTrans (acKey, acDoctype, adDate, adDateOfEntry, anDebit, anCredit, acLocked, acNote)
	values('1949000000001', '4900', '2019-12-31', '2019-12-31', 0, 0, 'F', 'Zakljuna knjienja 2019')
end
-----------------------------------------------------------------------------------------------------------
--alter table tHE_AcctTrans disable trigger all
--alter table tHE_AcctTransItem disable trigger all

--delete tHE_AcctTransItem where acKey = '2040000000001'
--delete tHE_AcctTransItem where acKey = '1949000000001'

--delete tHE_AcctTrans where acKey = '2040000000001'
--delete tHE_AcctTrans where acKey = '1949000000001'
go
-----------------------------------------------------------------------------------------------------------

if object_id('tempdb..#t') is not null
drop table #t
go
-----------------------------------------------------------------------------------------------------------

create table #t
(acAcct char (13),
acSubject char (30),
acDept char (30),
acCostDrv char (16),
acDoc varchar (35),
acLinkDoc varchar (35),
acLnkKey char (13),
anFXRate decimal(12,6),
acVAT char (1),
acCurrency char (3),
SaldoKM money,
SaldoVal money)


-- po subjektima
insert into #t
select p.acAcct, p.acSubject, '', '', 'PS 2020', 'PS 2020', '', 1, 'F', 'KM',
sum(p.anDebit-p.anCredit) as SaldoKM, sum(p.anDebit-p.anCredit) as SaldoVal
from tHE_AcctTrans g
inner join tHE_AcctTransItem p on g.acKey = p.acKey
where g.adDate between '2019-01-01' and '2019-12-31'
and p.acAcct in (select distinct acAcct from tHE_AcctTransItem where acSubject not like '' and  left(acAcct,2) in ('43', '20')) --21 FBiH
and left(p.acAcct,1) not in ('5','6')
group by p.acAcct, p.acSubject
having sum(p.anDebit-p.anCredit) <> 0

--Samo konta
insert into #t
select p.acAcct, '', '', '', 'PS 2020', 'PS 2020', '', 1, 'F', 'KM',
sum(p.anDebit-p.anCredit) as SaldoKM, sum(p.anDebit-p.anCredit) as SaldoVal
from tHE_AcctTrans g
inner join tHE_AcctTransItem p on g.acKey = p.acKey
where g.adDate between '2019-01-01' and '2019-12-31'
and p.acAcct not in (select distinct acAcct from tHE_AcctTransItem where acSubject not like '' and left(acAcct,2) in ('43', '20')) --21 FBiH
and left(p.acAcct,1) not in ('5','6')
group by p.acAcct
having sum(p.anDebit-p.anCredit) <> 0
go

alter table #t
add anDebit money default 0 not null,
anCredit money default 0 not null,
anValDebit money default 0 not null,
anValCredit money default 0 not null
go

update #t
set anDebit = SaldoKM
where SaldoKM > 0

update #t
set anCredit = SaldoKM * -1
where SaldoKM < 0

update #t
set anValDebit = SaldoVal
where SaldoVal > 0

update #t
set anValCredit = SaldoVal * -1
where SaldoVal < 0
go

alter table #t
add acKey char (13) default '2040000000001' not null,
anNo int identity (1,1) not null
go

insert into tHE_AcctTransItem (acKey, anNo, acAcct, acSubject, acDept, acCostDrv, acDoc, acLinkDoc, acLnkKey, anFXRate, acVATPayed, acCurrency, anDebit, anCredit, anValDebit, anValCredit, adDateDoc, adDateDue, adDateVAT)
select acKey, anNo, acAcct, acSubject, acDept, acCostDrv, acDoc, acLinkDoc, acLnkKey, anFXRate, acVAT, acCurrency, anDebit, anCredit, anValDebit, anValCredit, '2020-01-01', '2020-01-01', '2020-01-01'
from #t

update #t
set acKey = '1949000000001'

insert into tHE_AcctTransItem (acKey, anNo, acAcct, acSubject, acDept, acCostDrv, acDoc, acLinkDoc, acLnkKey, anFXRate, acVATPayed, acCurrency, anDebit, anCredit, anValDebit, anValCredit, adDateDoc, adDateDue, adDateVAT)
select acKey, anNo, acAcct, acSubject, acDept, acCostDrv, '', '', '', anFXRate, acVAT, acCurrency, anCredit, anDebit, anValCredit, anValDebit, '2019-12-31', '2019-12-31', '2019-12-31'
from #t

-----------------------------------------------------------------------------------------------------------

--delete tHE_CloseOpenLog
--delete tHE_CloseOpenScenario

alter table tHE_CloseOpenList nocheck constraint all
alter table tHE_CloseOpenAcctTrans nocheck constraint all
alter table tHE_AcctTransItem nocheck constraint all
alter table tHE_AcctTrans nocheck constraint all

--delete tHE_CloseOpenList
--delete tHE_CloseOpenAcctTrans
-----------------------------------------------------------------------------------------------------------

if not exists (select * from tHE_CloseOpenList where acKey = '20ZAK00000001')
begin
    insert into tHE_CloseOpenList (acKey, acDocType, acDocTypeCloseTrans, acDocTypeOpenTrans, adDateFrom, adDateTo, anStatus, acKeyOpen, acCalcFX, acTransSubjAcct)
    values ('20ZAK00000001', 'ZAK0','4900', '4000', '2020-01-01','2020-12-31', 0, '', 'F', 'F')
end

if not exists (select * from tHE_CloseOpenList where acKey = '19ZAK00000001')
begin
	insert into tHE_CloseOpenList (acKey, acDocType, acDocTypeCloseTrans, acDocTypeOpenTrans, adDateFrom, adDateTo, anStatus, acKeyOpen, acCalcFX, acTransSubjAcct)
	values ('19ZAK00000001', 'ZAK0','4900', '4000', '2019-01-01','2019-12-31', 0, '20ZAK00000001', 'F', 'F')
end else
begin
	update tHE_CloseOpenList
	set acKeyOpen = '20ZAK00000001'
	where acKey = '19ZAK00000001'
end


if not exists (select * from tHE_CloseOpenAcctTrans where acKey = '19ZAK00000001')
begin
	insert into tHE_CloseOpenAcctTrans (acKey, anNo, anType, anTypeTrans, acKeyCloseAcctTrans, anStatus, acKeyOpen, anNoOpen)
	values ('19ZAK00000001', 1, 3, 0, '1949000000001', 0, '20ZAK00000001', 1)
end else
begin
	update tHE_CloseOpenAcctTrans
	set acKeyCloseAcctTrans = '1949000000001', acKeyOpen = '20ZAK00000001'
	where acKey = '19ZAK00000001'
end


if not exists (select * from tHE_CloseOpenAcctTrans where acKey = '20ZAK00000001')
begin
	insert into tHE_CloseOpenAcctTrans (acKey, anNo, anType, anTypeTrans, acKeyCloseAcctTrans, anStatus, acKeyOpen, anNoOpen)
	values ('20ZAK00000001', 1, 3, 1, '2040000000001', 0, '', 1)
end else
begin
	updatetHE_CloseOpenAcctTrans
	set acKeyCloseAcctTrans = '2040000000001',acKeyOpen = ''
	where acKey = '20ZAK00000001'
end

--select * from tHE_CloseOpenAcctTrans
--select * from tHE_CloseOpenList

------------------------------------------------------------------
alter table tHE_AcctTransItem disable trigger all

update tHE_AcctTransItem
set acKeyCloseAcctTrans = '1949000000001'
where acKey like '19%'

alter table tHE_AcctTransItem enable trigger all

------------------------------------------------------------------
update tPA_SysParam
set adGLDateClose = '2019-12-31', adGLDateOpen = '2020-01-01'